package com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.repository.impl;

import com.css.fxfzxczh.base.dict.repository.entity.DictItemEntity;
import com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.entity.HouMonData;
import com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.entity.HouMonDataCheckVo;
import com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.queryParams.HouMonDataQuaParams;
import com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.repository.HouMonDataRepository;
import com.css.fxfzxczh.modules.disasterSituationDataManage.houMonData.vo.TotalFloorageVO;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class HouMonDataRepositoryImpl implements HouMonDataRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Map<String, Object> getPage(HouMonDataQuaParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from xczh_house_monomer_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSampleCode())) {
            whereSql.append(" and sample_code like '%" + queParams.getSampleCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getStructureType())) {
            whereSql.append(" and structure_type  = '" + queParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getDestructionType())) {
            whereSql.append(" and destruction_type = '" + queParams.getDestructionType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status = '" + queParams.getCheckStatus() + "'");
        }

        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouMonData.class)).size();
        List<HouMonData> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HouMonData.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public boolean save(HouMonData houMonData) {
        String sql="insert into xczh_house_monomer_data(id,xsem_id,sample_code,province,city,area,address,sampling_point_id,sampling_point_name,longitude,latitude,purpose_type," +
                "structure_type,destruction_type,floorage,is_typical_house,loss_value,property_loss_proportion,is_medium_and_high,create_user_name," +
                "create_user,create_time,update_user,update_time,del_flag,enclosure_photo,check_status,check_opinion,name,specific_description,enclosure_video,enclosure_audio,enclosure_album,geom) " +
                "values(:id, :xsemId, :sampleCode,:province, :city, :area, :address, :samplingPointId, :samplingPointName, :longitude, :latitude, :purposeType, :structureType," +
                ":destructionType, :floorage, :isTypicalHouse, :lossValue, :propertyLossProportion, :isMediumAndHigh, :createUserName, " +
                ":createUser, :createTime,:updateUser, :updateTime, :delFlag, :enclosurePhoto, :checkStatus, :checkOpinion, :name, :specificDescription, :enclosureVideo, :enclosureAudio, :enclosureAlbum,ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houMonData);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    @Override
    public List<HouMonData> getHouMonDataList(HouMonDataQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select * from xczh_house_monomer_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0'  ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSampleCode())) {
            whereSql.append(" and sample_code like '%" + queParams.getSampleCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getStructureType())) {
            whereSql.append(" and structure_type  = '" + queParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getDestructionType())) {
            whereSql.append(" and destruction_type = '" + queParams.getDestructionType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status = '" + queParams.getCheckStatus() + "'");
        }

        sql.append(whereSql).append(orderSql);
        List<HouMonData> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouMonData.class));

        return list;
    }

    @Override
    public String getMaxNum(String str) {
        String sql="select max(sample_code) from xczh_house_monomer_data where del_flag = '0' and sample_code like '%"+str+"%'";
        String maxNum=jdbcTemplate.queryForObject(sql,String.class);
        return maxNum;
    }

    @Override
    public HouMonData findById(String id) {
        String sql="select  * from xczh_house_monomer_data  where id = '"+id+"'";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(HouMonData.class));
    }

    @Override
    public void check(String id, String checkStatus, String checkOpinion) {
        String sql = "update xczh_house_monomer_data set check_status = ? , check_opinion = ? where id = ?";
        jdbcTemplate.update(sql,checkStatus,checkOpinion,id);
    }

    @Override
    public List<HouMonDataCheckVo> getHouMonCheckDataList(HouMonDataQuaParams queParams) {
        StringBuilder sql = new StringBuilder("select * from xczh_house_monomer_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and check_status!='0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSampleCode())) {
            whereSql.append(" and sample_code like '%" + queParams.getSampleCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getStructureType())) {
            whereSql.append(" and structure_type  = '" + queParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getDestructionType())) {
            whereSql.append(" and destruction_type = '" + queParams.getDestructionType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status = '" + queParams.getCheckStatus() + "'");
        }

        sql.append(whereSql).append(orderSql);
        List<HouMonDataCheckVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouMonDataCheckVo.class));

        return list;
    }

    @Override
    public void update(HouMonData houMonData) {
        String sql="update xczh_house_monomer_data set sampling_point_id = :samplingPointId ,sampling_point_name = :samplingPointName ," +
                "name = :name ,structure_type = :structureType ,destruction_type = :destructionType ,floorage = :floorage ,purpose_type = :purposeType ," +
                "is_typical_house = :isTypicalHouse ,loss_value = :lossValue ,enclosure_photo = :enclosurePhoto ," +
                "enclosure_video = :enclosureVideo ,enclosure_audio = :enclosureAudio ,enclosure_album = :enclosureAlbum, property_loss_proportion = :propertyLossProportion ," +
                "is_medium_and_high = :isMediumAndHigh ,specific_description = :specificDescription ," +
                "update_user = :updateUser, update_time = :updateTime where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(houMonData);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<HouMonData> getBySamplingPointId(String samplingPointId) {
        String sql="select  * from xczh_house_monomer_data  where sampling_point_id = '"+samplingPointId+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(HouMonData.class));
    }

    @Override
    public void submit(String id) {
        String sql = "update xczh_house_monomer_data set check_status= ? where id = ?";
        jdbcTemplate.update(sql, "1", id);
    }

    @Override
    public void updateXsemId(String xsemId, String convertedXsemId) {
        String sql = "update xczh_house_monomer_data set xsem_id = '"+xsemId+"'  where xsem_id = '"+convertedXsemId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public void delete(String[] ids) {
        String sql = "update xczh_house_monomer_data set del_flag= ? where id in ('"+String.join("','",ids)+"')";
        jdbcTemplate.update(sql, "1");
    }

    @Override
    public Map<String, Object> getAppPage(HouMonDataQuaParams queParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from xczh_house_monomer_data");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' ");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getSamplingPointName())) {
            whereSql.append(" and sampling_point_name like '%" + queParams.getSamplingPointName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getSampleCode())) {
            whereSql.append(" and sample_code like '%" + queParams.getSampleCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getStructureType())) {
            whereSql.append(" and structure_type  = '" + queParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getDestructionType())) {
            whereSql.append(" and destruction_type = '" + queParams.getDestructionType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and xsem_id = '" + queParams.getXsemId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and check_status = '" + queParams.getCheckStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUser())) {
            whereSql.append(" and create_user = '" + queParams.getCreateUser() + "'");
        }

        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(HouMonData.class)).size();
        List<HouMonData> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(HouMonData.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<TotalFloorageVO> getTotalFloorageByXstmId(String xstmId,List<String> ids) {
        String sql="select a.structure_type structureType,sum(cast(a.floorage as DECIMAL(10,2))) floorage " +
                "from xczh_house_monomer_data a left join xczh_sampling_point_data b on a.sampling_point_id=b.id and b.del_flag='0' " +
                "left join xczh_survey_task_manage c on b.xstm_id=c.xstm_id and c.del_flag='0' " +
                "where a.del_flag='0' " +
                "and a.check_status='2' " +
                "and (c.xstm_id= :xstmId " +
                "or b.id in (:ids) )" +
                "group by a.structure_type";
        Map idMap = new HashMap();
        idMap.put("ids", ids);
        idMap.put("xstmId", xstmId);
        List list = namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(TotalFloorageVO.class));
        return list;
    }
}
